use rusqlite::{Connection, Result};

#[derive(Debug)]
struct User {
    id: i32,
    name: String,
    data: Option<String>,
}

#[test]
fn test1() {
    let conn = Connection::open_in_memory().unwrap();

    conn.execute(r#"
    create table users (
        id integer primary key,
        name text not null,
        data blob
    )
    "#, ()).unwrap();


    let user = User {
        id: 0,
        name: "嘿嘿".to_string(),
        data: None,
    };
    conn.execute(
        "insert into users (name, data) values (?1, ?2)",
        (&user.name, &user.data),
    ).unwrap();

    let mut stmt = conn.prepare("select * from users").unwrap();
    let rows = stmt.query_map([], |r| {
        Ok(User {
            id: r.get(0).unwrap(),
            name: r.get(1).unwrap(),
            data: r.get(2).unwrap(),
        })
    }).unwrap();

    for x in rows {
        let u = x.unwrap();
        println!("{:?}", u);
        println!("id:{}", u.id);
    }
}

#[test]
fn test2() {
    let conn = Connection::open("test.sqlite").unwrap();
    let mut stmt = conn.prepare("select * from users").unwrap();
    let rows = stmt.query_map([], |r| {
        Ok(User {
            id: r.get(0).unwrap(),
            name: r.get(1).unwrap(),
            data: r.get(2).unwrap(),
        })
    }).unwrap();

    for x in rows {
        let u = x.unwrap();
        println!("{:?}", u);
        println!("id:{}", u.id);
    }
}

#[test]
fn test3() -> Result<()> {
    let conn = Connection::open_in_memory()?;

    conn.execute(r#"
    create table users (
        id integer primary key,
        name text not null,
        address text
    )
    "#, ())?;

    conn.execute("insert into users (name,address) values (?1,?2)", ("张三", "辽宁省大连市"))?;
    conn.execute("insert into users (name,address) values (?1,?2)", ("李四", "辽宁省沈阳市"))?;

    let mut stmt = conn.prepare("select * from users where address like '%大连%'")?;
    let users = stmt.query_map([], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            data: row.get(2)?,
        })
    })?;
    for x in users {
        let user = x.unwrap();
        println!("user:{:?}", user);
    }

    Ok(())
}